/**
 * This file is part of Opintopaivakirja.
 *
 * Opintopaivakirja is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * Opintopaivakirja is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Opintopaivakirja.  If not, see <http://www.gnu.org/licenses/>.
 */
package opintopaivakirja;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

/**
 * Luokka toteuttaa järjestelmään tietokantayhteyden.
 *
 * @author Teemu Jonkkari
 * @version 1.0
 */
public class Tietokanta {

    private Connection conn;
    private String framework = "embedded"; // oletus
    private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    private String protocol = "jdbc:derby:db_";
    

    /**
     * Oletusmuodostin
     */
    public Tietokanta() {
    }

    /**
     * Metodi palauttaa kutsujalle yhteyden tietokantaan.
     *
     * @return Connection-olio.
     */
    public Connection getYhteys() {

        loadDriver();
        Properties p = new Properties();
        p.put("user", "opintopaivakirja");
        p.put("password", "0p3nt0");
        String dbName = "opintopaivakirja";

        try {
            conn = DriverManager.getConnection(protocol + dbName + ";create=true", p); // Lopulliseen ;dataEncryption=true;bootPassword=DBpassword", p);
        } catch (SQLException ex) {
            Virheenkasittely.getInstance()
                    .setVirhe(Tietokanta.class.getName(), ex);
        } finally {
            return conn;
        }
    }

    /**
     * Metodilla voidaan sammuttaa tietokantasovellus kun ohjelma suljetaan.
     */
    public void sammutaTietokanta() {
        try {
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (SQLException se) {
            if (((se.getErrorCode() == 50000) && ("XJ015".equals(se.getSQLState())))) {
                System.out.println("Derby shut down normally");
            // Note that for single database shutdown, the expected
            // SQL state is "08006", and the error code is 45000.
            } else {
                System.err.println("Derby did not shut down normally");
            }
        }
    }

    /**
     * Metodilla voidaan luoda opintopaivakirjan tietokanta.
     */
    public void luoTietokanta() {
        ArrayList<String> statements = this.luoTietokantaTaulut();
        conn = this.getYhteys();
        Statement s = null;

        try {
            s = conn.createStatement();

            for (String lause : statements) {
                s.execute(lause);
            }

        } catch (SQLException ex) {
            Virheenkasittely.getInstance()
                    .setVirhe(Tietokanta.class.getName(), ex);
        }

        try {
            if (s != null) {
                s.close();
                s = null;
            }
        } catch (SQLException ex) {
            Virheenkasittely.getInstance()
                    .setVirhe(Tietokanta.class.getName(), ex);
        }
    }

    /**
     * Metodi muodostaa SQL-lauseet tietokannan luomista varten.
     * 
     * @return ArrayList, jossa tarvittavat käskyt.
     */
    private ArrayList<String> luoTietokantaTaulut() {
        ArrayList<String> statements = new ArrayList<String>();

        final String muistiinpano =
                "CREATE TABLE OPINTOPAIVAKIRJA.MUISTIINPANO " +
                "(ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, " +
                "OTSIKKO VARCHAR(255) NOT NULL, " +
                "ASIA VARCHAR (30000) NOT NULL, " +
                "TYYPPI SMALLINT DEFAULT 1 NOT NULL, " +
                "JARJESTYS SMALLINT DEFAULT 0 NOT NULL, " +
                "PRIMARY KEY (ID)) ";
        statements.add(muistiinpano);

        final String muistiinpano_index =
                "CREATE INDEX OPINTOPAIVAKIRJA.MUISTIINPANO_INDEX " +
                "ON OPINTOPAIVAKIRJA.MUISTIINPANO (TYYPPI, JARJESTYS, OTSIKKO)";
        statements.add(muistiinpano_index);

        final String kayttaja =
                "CREATE TABLE OPINTOPAIVAKIRJA.KAYTTAJA " +
                "(ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, " +
                "ETUNIMI VARCHAR(50) NOT NULL, " +
                "SUKUNIMI VARCHAR(50) NOT NULL, " +
                "SAHKOPOSTI VARCHAR(255) NOT NULL, " +
                "OTUNNUS VARCHAR(25) NOT NULL, " +
                "RTUNNUS VARCHAR(25) NOT NULL, " +
                "KAYTTAJATUNNUS VARCHAR(255) NOT NULL, " +
                "SALASANA VARCHAR(255) NOT NULL, " +
                "PPOISTO SMALLINT DEFAULT 0 NOT NULL, " +
                "PRIMARY KEY (ID)) ";
        statements.add(kayttaja);

        final String kayttaja_index =
                "CREATE UNIQUE INDEX OPINTOPAIVAKIRJA.KAYTTAJA_UNIQUE " +
                "ON OPINTOPAIVAKIRJA.KAYTTAJA (KAYTTAJATUNNUS) ";
        statements.add(kayttaja_index);

        final String tagi =
                "CREATE TABLE OPINTOPAIVAKIRJA.TAGI " +
                "(MUISTIINPANO_ID INTEGER NOT NULL, " +
                "SANA VARCHAR(50) NOT NULL, " +
                "PRIMARY KEY (MUISTIINPANO_ID, SANA), " +
                "FOREIGN KEY(MUISTIINPANO_ID) " +
                "REFERENCES MUISTIINPANO(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION) ";
        statements.add(tagi);

        final String tagi_fk_index =
                "CREATE INDEX OPINTOPAIVAKIRJA.TAGI_FK " +
                "ON OPINTOPAIVAKIRJA.TAGI (MUISTIINPANO_ID) ";
        statements.add(tagi_fk_index);

        final String linkki =
                "CREATE TABLE OPINTOPAIVAKIRJA.LINKKI " +
                "(ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, " +
                "MUISTIINPANO_ID INTEGER NOT NULL, " +
                "OTSIKKO VARCHAR(255) NOT NULL, " +
                "URL VARCHAR(255) NOT NULL, " +
                "PRIMARY KEY(ID, MUISTIINPANO_ID), " +
                "FOREIGN KEY(MUISTIINPANO_ID) " +
                "REFERENCES MUISTIINPANO(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION) ";
        statements.add(linkki);

        final String linkki_fk_index =
                "CREATE INDEX OPINTOPAIVAKIRJA.LINKKI_FK " +
                "ON OPINTOPAIVAKIRJA.LINKKI (MUISTIINPANO_ID) ";
        final String linkki_unique =
                "CREATE UNIQUE INDEX OPINTOPAIVAKIRJA.LINKKI_UNIQUE " +
                "ON OPINTOPAIVAKIRJA.LINKKI (MUISTIINPANO_ID, OTSIKKO, URL) ";
        final String linkki_index =
                "CREATE INDEX OPINTOPAIVAKIRJA.LINKKI_INDEX " +
                "ON OPINTOPAIVAKIRJA.LINKKI (OTSIKKO) ";
        statements.add(linkki_unique);
        statements.add(linkki_index);
        statements.add(linkki_fk_index);

        final String projekti =
                "CREATE TABLE OPINTOPAIVAKIRJA.PROJEKTI " +
                "(ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, " +
                "KAYTTAJA_ID INTEGER NOT NULL, " +
                "PROJEKTI_ID INTEGER, " +
                "NIMI VARCHAR(255) NOT NULL, " +
                "ESITTELY VARCHAR(30000) NOT NULL, " +
                "ARVIOINTI VARCHAR(30000) NOT NULL, " +
                "LINKKI VARCHAR(255) NOT NULL, " +
                "PVM_ALKU DATE DEFAULT CURRENT_DATE NOT NULL, " +
                "PVM_LOPPU DATE DEFAULT DATE('2017-12-06') NOT NULL, " +
                "PVM_PAIVITETTY TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, " +
                "ARVOSANA SMALLINT DEFAULT 0 NOT NULL, " +
                "ARVOSANA_OMA SMALLINT DEFAULT 0 NOT NULL, " +
                "ASEMA SMALLINT DEFAULT 0 NOT NULL, " +
                "PRIMARY KEY (ID), " +
                "FOREIGN KEY(PROJEKTI_ID) " +
                "REFERENCES Projekti(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION, " +
                "FOREIGN KEY(KAYTTAJA_ID) " +
                "REFERENCES KAYTTAJA(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION) ";
        statements.add(projekti);

        final String projekti_fk_index1 =
                "CREATE INDEX OPINTOPAIVAKIRJA.PROJEKTI_FK1 " +
                "ON OPINTOPAIVAKIRJA.PROJEKTI (PROJEKTI_ID) ";
        final String projekti_unique =
                "CREATE UNIQUE INDEX PROJEKTI_UNIQUE " +
                "ON OPINTOPAIVAKIRJA.PROJEKTI (NIMI, KAYTTAJA_ID, PROJEKTI_ID) ";
        final String projekti_fk_index2 =
                "CREATE INDEX OPINTOPAIVAKIRJA.PROJEKTI_FK2 " +
                "ON OPINTOPAIVAKIRJA.PROJEKTI (KAYTTAJA_ID) ";
        final String projekti_index =
                "CREATE INDEX OPINTOPAIVAKIRJA.PROJEKTI_INDEX " +
                "ON OPINTOPAIVAKIRJA.PROJEKTI (KAYTTAJA_ID, NIMI) ";
        statements.add(projekti_unique);
        statements.add(projekti_index);
        statements.add(projekti_fk_index1);
        statements.add(projekti_fk_index2);

        final String tavoite_has_muistiinpano =
                "CREATE TABLE TAVOITE_HAS_MUISTIINPANO " +
                "(MUISTIINPANO_ID INTEGER NOT NULL, " +
                "TAVOITE_ID INTEGER NOT NULL, " +
                "PRIMARY KEY(MUISTIINPANO_ID, TAVOITE_ID), " +
                "FOREIGN KEY(TAVOITE_ID) " +
                "REFERENCES MUISTIINPANO(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION, " +
                "FOREIGN KEY(MUISTIINPANO_ID) " +
                "REFERENCES MUISTIINPANO(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION) ";
        statements.add(tavoite_has_muistiinpano);

        final String tavoite_has_muistiinpano_fk_index1 =
                "CREATE INDEX OPINTOPAIVAKIRJA.TAVOITE_HAS_MUISTIINPANO_FK1 " +
                "ON OPINTOPAIVAKIRJA.TAVOITE_HAS_MUISTIINPANO (TAVOITE_ID) ";
        final String tavoite_has_muistiinpano_fk_index2 =
                "CREATE INDEX OPINTOPAIVAKIRJA.TAVOITE_HAS_MUISTIINPANO_FK2 " +
                "ON OPINTOPAIVAKIRJA.TAVOITE_HAS_MUISTIINPANO (MUISTIINPANO_ID) ";
        statements.add(tavoite_has_muistiinpano_fk_index1);
        statements.add(tavoite_has_muistiinpano_fk_index2);

        final String projekti_has_muistiinpano =
                "CREATE TABLE PROJEKTI_HAS_MUISTIINPANO " +
                "(MUISTIINPANO_ID INTEGER NOT NULL, " +
                "PROJEKTI_ID INTEGER NOT NULL, " +
                "PRIMARY KEY(MUISTIINPANO_ID, PROJEKTI_ID), " +
                "FOREIGN KEY(MUISTIINPANO_ID) " +
                "REFERENCES MUISTIINPANO(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION, " +
                "FOREIGN KEY(PROJEKTI_ID) " +
                "REFERENCES PROJEKTI(ID) " +
                "ON DELETE CASCADE " +
                "ON UPDATE NO ACTION) ";
        statements.add(projekti_has_muistiinpano);

        final String projekti_has_muistiinpano_fk_index1 =
                "CREATE INDEX OPINTOPAIVAKIRJA.PROJEKTI_HAS_MUISTIINPANO_FK1 " +
                "ON OPINTOPAIVAKIRJA.PROJEKTI_HAS_MUISTIINPANO (MUISTIINPANO_ID) ";
        final String projekti_has_muistiinpano_fk_index2 =
                "CREATE INDEX OPINTOPAIVAKIRJA.PROJEKTI_HAS_MUISTIINPANO_FK2 " +
                "ON OPINTOPAIVAKIRJA.PROJEKTI_HAS_MUISTIINPANO (PROJEKTI_ID) ";
        statements.add(projekti_has_muistiinpano_fk_index1);
        statements.add(projekti_has_muistiinpano_fk_index2);

        /* TRIGGERI MUISTIINPANOJEN POISTAMISEEN KUN NE OMISTAVA PROJEKTI POISTETAAN. */
        final String del_m_when_p_del_trigger =
                "CREATE TRIGGER OPINTOPAIVAKIRJA.DEL_M_WHEN_P_DEL " +
                "AFTER DELETE ON OPINTOPAIVAKIRJA.PROJEKTI_HAS_MUISTIINPANO " +
                "REFERENCING OLD AS DELETEDROWS " +
                "FOR EACH ROW " +
                "DELETE FROM OPINTOPAIVAKIRJA.MUISTIINPANO " +
                "WHERE MUISTIINPANO.ID = DELETEDROWS.MUISTIINPANO_ID ";
        statements.add(del_m_when_p_del_trigger);

        return statements;

    }

    /**
     * Metodi lataa jdbc-ajurin tietokantayhteyttä varten.
     */
    private void loadDriver() {
        /*
         *  The JDBC driver is loaded by loading its class.
         *  If you are using JDBC 4.0 (Java SE 6) or newer, JDBC drivers may
         *  be automatically loaded, making this code optional.
         *
         *  In an embedded environment, this will also start up the Derby
         *  engine (though not any databases), since it is not already
         *  running. In a client environment, the Derby engine is being run
         *  by the network server framework.
         *
         *  In an embedded environment, any static Derby system properties
         *  must be set before loading the driver to take effect.
         */
        try {
            Class.forName(driver).newInstance();
        //System.out.println("Loaded the appropriate driver");
        } catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
        } catch (InstantiationException ie) {
            System.err.println(
                    "\nUnable to instantiate the JDBC driver " + driver);
            ie.printStackTrace(System.err);
        } catch (IllegalAccessException iae) {
            System.err.println(
                    "\nNot allowed to access the JDBC driver " + driver);
            iae.printStackTrace(System.err);
        }
    }
}
